CREATE TABLE "Categories" ("VariableID" INTEGER PRIMARY KEY  NOT NULL ,"DataValue" float NOT NULL ,"CategoryDescription" nvarchar NOT NULL );
CREATE TABLE "DataFiles" ("FileID" INTEGER PRIMARY KEY NOT NULL ,"FileName" nvarchar NOT NULL  DEFAULT '' ,"FileDescription" nvarchar NOT NULL  DEFAULT '' ,"FilePath" nvarchar NOT NULL  DEFAULT '' ,"FileOrigin" nvarchar NOT NULL  DEFAULT '' ,"LoadMethod" nvarchar NOT NULL  DEFAULT '' ,"LoadDateTime" DATETIME NOT NULL ,"QueryID" INTEGER DEFAULT 0, "FileType" nvarchar NOT NULL  DEFAULT "");
CREATE TABLE "DataSeries" ("SeriesID" INTEGER PRIMARY KEY  NOT NULL ,"SiteID" INTEGER NOT NULL ,"VariableID" INTEGER NOT NULL ,"IsCategorical" bit NOT NULL ,"MethodID" INTEGER NOT NULL ,"SourceID" INTEGER NOT NULL ,"QualityControlLevelID" INTEGER NOT NULL ,"BeginDateTime" DATETIME NOT NULL ,"EndDateTime" DATETIME NOT NULL ,"BeginDateTimeUTC" DATETIME NOT NULL ,"EndDateTimeUTC" DATETIME NOT NULL ,"ValueCount" INTEGER NOT NULL ,"CreationDateTime" DATETIME NOT NULL ,"Subscribed" bit,"UpdateDateTime" DATETIME,"LastcheckedDateTime" DATETIME);
CREATE TABLE "DataServices" ("ServiceID" INTEGER PRIMARY KEY  NOT NULL ,"ServiceCode" nvarchar NOT NULL  DEFAULT '' ,"ServiceName" nvarchar NOT NULL  DEFAULT '' ,"ServiceType" nvarchar NOT NULL  DEFAULT '' ,"ServiceVersion" numeric NOT NULL ,"ServiceProtocol" nvarchar NOT NULL  DEFAULT '' ,"ServiceEndpointURL" nvarchar NOT NULL  DEFAULT '' ,"ServiceDescriptionURL" nvarchar NOT NULL  DEFAULT '' ,"NorthLatitude" float,"SouthLatitude" float,"EastLongitude" float,"WestLongitude" float,"Abstract" nvarchar DEFAULT '' ,"ContactName" nvarchar DEFAULT '' ,"ContactEmail" nvarchar DEFAULT '' ,"Citation" nvarchar DEFAULT '' , "ServiceTitle" nvarchar);
CREATE TABLE "DataThemeDescriptions" ("ThemeID" INTEGER PRIMARY KEY  NOT NULL ,"ThemeName" nvarchar NOT NULL  DEFAULT '' ,"ThemeDescription" nvarchar DEFAULT '' ,"DateCreated" DATETIME);
CREATE TABLE "DataThemes" ("ThemeID" INTEGER NOT NULL ,"SeriesID" INTEGER NOT NULL  DEFAULT 0 , PRIMARY KEY ("ThemeID","SeriesID") );
CREATE TABLE "DataValues" ("ValueID" INTEGER PRIMARY KEY NOT NULL ,"SeriesID" INTEGER NOT NULL ,"DataValue" float NOT NULL ,"ValueAccuracy" float,"LocalDateTime" DATETIME NOT NULL ,"UTCOffset" float NOT NULL ,"DateTimeUTC" DATETIME NOT NULL ,"OffsetValue" float,"OffsetTypeID" INTEGER,"CensorCode" nvarchar,"QualifierID" INTEGER,"SampleID" INTEGER,"FileID" INTEGER);
CREATE TABLE "ISOMetadata" ("MetadataID" INTEGER PRIMARY KEY  NOT NULL ,"TopicCategory" nvarchar NOT NULL  DEFAULT 'Unknown' ,"Title" nvarchar NOT NULL  DEFAULT 'Unknown' ,"Abstract" nvarchar NOT NULL  DEFAULT 'Unknown' ,"ProfileVersion" nvarchar NOT NULL  DEFAULT 'Unknown' ,"MetadataLink" nvarchar);
CREATE TABLE "LabMethods" ("LabMethodID" INTEGER PRIMARY KEY  NOT NULL ,"LabName" nvarchar NOT NULL  DEFAULT '' ,"LabOrganization" nvarchar NOT NULL  DEFAULT '' ,"LabMethodName" nvarchar NOT NULL  DEFAULT '' ,"LabMethodLink" nvarchar NOT NULL  DEFAULT '' , "LabMethodDescription" nvarchar NOT NULL  DEFAULT "");
CREATE TABLE "Methods" ("MethodID" INTEGER PRIMARY KEY  NOT NULL ,"MethodDescription" nvarchar NOT NULL  DEFAULT '' ,"MethodLink" nvarchar NOT NULL  DEFAULT '' );
CREATE TABLE "OffsetTypes" ("OffsetTypeID" INTEGER PRIMARY KEY NOT NULL ,"OffsetUnitsID" INTEGER NOT NULL ,"OffsetDescription" nvarchar NOT NULL);
CREATE TABLE "OntologyConcepts" ("ConceptID" INTEGER PRIMARY KEY  NOT NULL ,"ConceptCode" nvarchar NOT NULL  DEFAULT '' ,"Concept" nvarchar NOT NULL  DEFAULT '' ,"ConceptDefinition" nvarchar NOT NULL  DEFAULT '' );
CREATE TABLE "Points" ("PointID" INTEGER PRIMARY KEY  NOT NULL  DEFAULT 0 ,"SiteID" INTEGER NOT NULL  DEFAULT 0 ,"PointType" nvarchar NOT NULL  DEFAULT '' );
CREATE TABLE "Qualifiers" ("QualifierID" INTEGER PRIMARY KEY  NOT NULL ,"QualifierCode" nvarchar DEFAULT '' ,"QualifierDescription" nvarchar NOT NULL  DEFAULT '' );
CREATE TABLE "QualityControlLevels" ("QualityControlLevelID" INTEGER PRIMARY KEY  NOT NULL ,"QualityControlLevelCode" nvarchar NOT NULL ,"Definition" nvarchar NOT NULL ,"Explanation" nvarchar NOT NULL );
CREATE TABLE "Queries" ("QueryID" INTEGER PRIMARY KEY  NOT NULL ,"LocationParameter" nvarchar NOT NULL  DEFAULT '' ,"VariableParameter" nvarchar NOT NULL  DEFAULT '' ,"BeginDateParameter" DATETIME NOT NULL ,"EndDateParameter" DATETIME NOT NULL ,"AuthenticationToken" nvarchar NOT NULL  DEFAULT '' ,"QueryDateTime" DATETIME NOT NULL ,"ServiceID" INTEGER NOT NULL  DEFAULT 0 );
CREATE TABLE "Samples" ("SampleID" INTEGER PRIMARY KEY NOT NULL ,"SampleType" nvarchar NOT NULL  DEFAULT '' ,"LabSampleCode" nvarchar NOT NULL  DEFAULT '' ,"LabMethodID" INTEGER NOT NULL  DEFAULT 0 );
CREATE TABLE Selection(SeriesID INTEGER PRIMARY KEY NOT NULL);
CREATE TABLE "SeriesAnnotation" ("AnnotationID" INTEGER NOT NULL ,"SeriesID" INTEGER NOT NULL  DEFAULT 0 ,"NoteDateTime" DATETIME NOT NULL ,"Note" nvarchar NOT NULL  DEFAULT '' , PRIMARY KEY ("AnnotationID","SeriesID") );
CREATE TABLE "SeriesProvenance" ("ProvenanceID" INTEGER NOT NULL ,"ProvenanceDateTime" DATETIME NOT NULL ,"InputSeriesID" INTEGER NOT NULL  DEFAULT 0 ,"OutputSeriesID" INTEGER NOT NULL  DEFAULT 0 ,"MethodID" INTEGER NOT NULL  DEFAULT 0 ,"Comment" nvarchar DEFAULT '' , PRIMARY KEY ("ProvenanceID","InputSeriesID","OutputSeriesID","MethodID") );
CREATE TABLE "Sites" ("SiteID" INTEGER PRIMARY KEY NOT NULL ,"SiteCode" nvarchar NOT NULL  DEFAULT '' ,"SiteName" nvarchar NOT NULL  DEFAULT '' ,"Latitude" float NOT NULL ,"Longitude" float NOT NULL ,"LatLongDatumID" INTEGER NOT NULL  DEFAULT 0 ,"Elevation_m" float,"VerticalDatum" nvarchar DEFAULT '' ,"LocalX" float,"LocalY" float,"LocalProjectionID" INTEGER DEFAULT 0 ,"PosAccuracy_m" float,"State" nvarchar DEFAULT '' ,"County" nvarchar DEFAULT '' ,"Comments" nvarchar DEFAULT '' );
CREATE TABLE "Sources" ("SourceID" INTEGER PRIMARY KEY  NOT NULL ,"Organization" nvarchar NOT NULL  DEFAULT '' ,"SourceDescription" nvarchar NOT NULL  DEFAULT '' ,"SourceLink" nvarchar NOT NULL  DEFAULT '' ,"ContactName" nvarchar NOT NULL  DEFAULT '' ,"Phone" nvarchar NOT NULL  DEFAULT 0 ,"Email" nvarchar NOT NULL  DEFAULT '' ,"Address" nvarchar NOT NULL  DEFAULT '' ,"City" nvarchar NOT NULL  DEFAULT '' ,"State" nvarchar NOT NULL  DEFAULT '' ,"ZipCode" numeric NOT NULL  DEFAULT 0 ,"Citation" nvarchar NOT NULL  DEFAULT '' ,"MetadataID" INTEGER NOT NULL  DEFAULT 0 );
CREATE TABLE "SpatialReferences" ("SpatialReferenceID" INTEGER PRIMARY KEY  NOT NULL  DEFAULT 0 ,"SRSID" INTEGER NOT NULL  DEFAULT 0 ,"SRSName" nvarchar NOT NULL  DEFAULT '' ,"Notes" nvarchar DEFAULT '' );
CREATE TABLE "UnitConversions" ("ConversionID" INTEGER PRIMARY KEY NOT NULL  DEFAULT 0 ,"FromUnitsID" INTEGER NOT NULL  DEFAULT 0 ,"ToUnitsID" INTEGER NOT NULL  DEFAULT 0 ,"ConversionFactor" float NOT NULL  );
CREATE TABLE "Units" ("UnitsID" INTEGER PRIMARY KEY  NOT NULL ,"UnitsName" nvarchar NOT NULL ,"UnitsType" nvarchar NOT NULL ,"UnitsAbbreviation" nvarchar NOT NULL );
CREATE TABLE "Variables" ("VariableID" INTEGER PRIMARY KEY NOT NULL ,"VariableCode" nvarchar NOT NULL ,"VariableName" nvarchar NOT NULL ,"Speciation" nvarchar NOT NULL  DEFAULT 'Not Applicable' ,"VariableUnitsID" INTEGER NOT NULL ,"SampleMedium" nvarchar NOT NULL  DEFAULT 'Unknown' ,"ValueType" nvarchar NOT NULL  DEFAULT 'Unknown' ,"IsRegular" bit NOT NULL  DEFAULT 0 ,"IsCategorical" bit NOT NULL  DEFAULT 0 ,"TimeSupport" float NOT NULL  DEFAULT 0 ,"TimeUnitsID" INTEGER NOT NULL  DEFAULT 103 ,"DataType" nvarchar NOT NULL  DEFAULT 'Unknown' ,"GeneralCategory" nvarchar NOT NULL  DEFAULT 'Unknown' ,"NoDataValue" float NOT NULL  DEFAULT -9999 ,"ConceptID" INTEGER);
CREATE TABLE sqlite_stat1(tbl,idx,stat);
CREATE TABLE [sysdiagrams] (
	[name]		[nvarchar] NOT NULL,
	[principal_id]		[int] NOT NULL,
	[diagram_id]		[int] NOT NULL,
	[version]		[int] NULL,
	[definition]		[blob] NULL,
PRIMARY KEY ([diagram_id], [principal_id], [name])
);
CREATE INDEX "is_isoMetadata" ON "ISOMetadata" ("MetadataLink" ASC);
CREATE INDEX "ix_DataSeries" ON "DataSeries" ("SiteID" ASC, "VariableID" ASC, "MethodID" ASC, "SourceID" ASC, "QualityControlLevelID" ASC);
CREATE UNIQUE INDEX "ix_DataThemeDescriptionId" ON "DataThemeDescriptions" ("ThemeID" ASC);
CREATE UNIQUE INDEX "ix_MethodId" ON "Methods" ("MethodID" ASC);
CREATE INDEX "ix_dataService" ON "DataServices" ("ServiceEndpointURL" ASC);
CREATE UNIQUE INDEX "ix_dataServiceId" ON "DataServices" ("ServiceID" ASC);
CREATE INDEX "ix_dataTheme" ON "DataThemeDescriptions" ("ThemeName" ASC, "ThemeDescription" ASC);
CREATE UNIQUE INDEX "ix_dataseriesId" ON "DataSeries" ("SeriesID" ASC);
CREATE UNIQUE INDEX "ix_isoMdId" ON "ISOMetadata" ("MetadataID" ASC);
CREATE INDEX "ix_method" ON "Methods" ("MethodDescription" ASC, "MethodLink" ASC);
CREATE INDEX "ix_qc" ON "QualityControlLevels" ("QualityControlLevelCode" ASC, "Definition" ASC);
CREATE UNIQUE INDEX "ix_qcId" ON "QualityControlLevels" ("QualityControlLevelID" ASC);
CREATE INDEX "ix_qualfier" ON "Qualifiers" ("QualifierCode" ASC, "QualifierDescription" ASC);
CREATE UNIQUE INDEX "ix_qualifierId" ON "Qualifiers" ("QualifierID" ASC);
CREATE INDEX "ix_siteId" ON "Sites" ("SiteID" ASC);
CREATE INDEX "ix_siteSearch" ON "Sites" ("SiteCode" ASC);
CREATE UNIQUE INDEX "ix_sourceId" ON "Sources" ("SourceID" ASC);
CREATE INDEX "ix_sources" ON "Sources" ("Organization" ASC);
CREATE UNIQUE INDEX "ix_spatialReferencId" ON "SpatialReferences" ("SpatialReferenceID" ASC);
CREATE INDEX "ix_spatialReference" ON "SpatialReferences" ("SRSName" ASC);
CREATE INDEX "ix_unit" ON "Units" ("UnitsName" ASC, "UnitsAbbreviation" ASC);
CREATE UNIQUE INDEX "ix_unitId" ON "Units" ("UnitsID" ASC);
CREATE UNIQUE INDEX "ix_variableId" ON "Variables" ("VariableID" ASC);
CREATE INDEX "ix_variableSearch" ON "Variables" ("VariableCode" ASC);
CREATE UNIQUE INDEX [sysdiagrams_UK_principal_name]
ON [sysdiagrams]
([principal_id], [name]);
